﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace WebShopData
{
    public class Order
    {
        public Order(int userId, string firstName, string lastName, string address, string city, string zip, string email, string status = "Unprocessed", int orderId = 0)
        {
            OrderId = orderId;
            UserId = userId;
            FirstName = firstName;
            LastName = lastName;
            Address = address;
            City = city;
            Zip = zip;
            Email = email;
            Status = status;
            OrderRows = new List<OrderRow>();
        }

        public int OrderId { get; set; }
        public int UserId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Zip { get; set; }
        public string Email { get; set; }
        public string Status { get; set; }
        public List<OrderRow> OrderRows { get; set; }
        public long TotalSum
        {
            get
            {
                long result = 0;
                foreach (OrderRow row in OrderRows)
                {
                    result += row.Sum;
                }
                return result;
            }
        }

        public void AddOrderRow(OrderRow orderRow)
        {
            bool articleIdNotInList = true;
            foreach (OrderRow row in OrderRows)
            {
                if (row.Article.ArticleId == orderRow.Article.ArticleId)
                {
                    row.Quantity += orderRow.Quantity;
                    articleIdNotInList = false;
                    break;
                }
            }
            if (articleIdNotInList)
            {
                orderRow.OrderId = this.OrderId;
                OrderRows.Add(orderRow);
            }
        }

        public OrderRow GetOrderRow(int articleId)
        {
            foreach (OrderRow row in OrderRows)
            {
                if (row.Article.ArticleId == articleId)
                {
                    return row;
                }
            }
            return null;
        }

        public bool DeleteOrderRow(int articleId)
        {
            foreach (OrderRow row in OrderRows)
            {
                if (row.Article.ArticleId == articleId)
                {
                    return OrderRows.Remove(row);
                }
            }
            return false;
        }

        public override string ToString()
        {
            string result = string.Format("{0} {1} {2} {3} {4} {5}",
                OrderId, UserId, Address, City, Zip, Status);
            for (int i = 0; i < OrderRows.Count; i++)
            {
                result += string.Format("{0}. {1}", i + 1, OrderRows[i]);
            }
            return result;
        }
    }

    public static class OrderDB
    {
        public static int InsertNewOrder(Order order)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery =
                        new StringBuilder("INSERT INTO [dbo].[Orders]([UserId],[FirstName],[LastName],[Address],[Zip],[City],[Email],[Status])");
                    sqlQuery.Append(" VALUES(@UserId, @FirstName, @LastName, @Address, @Zip, @City, @Email, @Status)");
                    sqlQuery.Append(" SELECT @@IDENTITY");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("UserId", SqlDbType.Int).Value = order.UserId;
                    command.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value = order.FirstName;
                    command.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value = order.LastName;
                    command.Parameters.Add("Address", SqlDbType.VarChar, 50).Value = order.Address;
                    command.Parameters.Add("Zip", SqlDbType.VarChar, 10).Value = order.Zip;
                    command.Parameters.Add("City", SqlDbType.VarChar, 50).Value = order.City;
                    command.Parameters.Add("Email", SqlDbType.VarChar, 50).Value = order.Email;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = order.Status;

                    int identity = int.Parse(command.ExecuteScalar().ToString());
                    int affectedRows = 1;
                    foreach (OrderRow orderRow in order.OrderRows)
                    {
                        orderRow.OrderId = identity;
                        affectedRows += OrderRowDB.InsertNewOrderRow(orderRow);
                    }
                    return identity;
                }
            }
        }

        public static List<Order> GetAllOrders()
        {
            return GetOrdersBase("", new List<SqlParameter>());
        }

        public static Order GetOrderById(int orderId)
        {
            string whereLine = " WHERE [OrderId] = @orderId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("OrderId", SqlDbType.Int);
            sqlParameter.Value = orderId;
            parameters.Add(sqlParameter);
            List<Order> orders = GetOrdersBase(whereLine, parameters);
            if (orders.Count == 1)
            {
                return orders[0];
            }
            return null;
        }

        public static List<Order> GetOrdersBase(string whereLine, List<SqlParameter> parameters)
        {
            DataSet dataSetOrders = GetOrdersAsDataSet(whereLine, parameters);
            var orders = new List<Order>();
            foreach (DataRow row in dataSetOrders.Tables[0].Rows)
            {
                var order = new Order(
                    orderId: int.Parse(row["OrderId"].ToString().Trim()),
                    userId: int.Parse(row["UserId"].ToString().Trim()),
                    firstName: row["FirstName"].ToString().Trim(),
                    lastName: row["LastName"].ToString().Trim(),
                    address: row["Address"].ToString().Trim(),
                    city: row["City"].ToString().Trim(),
                    zip: row["Zip"].ToString().Trim(),
                    email: row["Email"].ToString().Trim(),
                    status: row["Status"].ToString().Trim());
                orders.Add(order);

                foreach (OrderRow orderRow in OrderRowDB.GetOrderRowsByOrderId(order.OrderId))
                {
                    order.AddOrderRow(orderRow);
                }
            }
            return orders;
        }

        public static DataSet GetOrdersAsDataSet(string whereLine, List<SqlParameter> parameters)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("SELECT [OrderId],[UserId],[FirstName],[LastName],[Address],[Zip],[City],[Email],[Status]");
                    sqlQuery.Append(" FROM [dbo].[OrdersNotDeletedView]");
                    sqlQuery.Append(whereLine);
                    command.CommandText = sqlQuery.ToString();
                    foreach (SqlParameter sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                    DataSet result = GalaxySale2014DB.GetDataSet(command);
                    return result;
                }
            }
        }

        public static int UpdateOrder(Order order)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("UPDATE [dbo].[Orders]");
                    sqlQuery.Append(" SET [UserId] = @UserId");
                    sqlQuery.Append(", [FirstName] = @FirstName");
                    sqlQuery.Append(", [LastName] = @LastName");
                    sqlQuery.Append(", [Address] = @Address");
                    sqlQuery.Append(", [Zip] = @Zip");
                    sqlQuery.Append(", [City] = @City");
                    sqlQuery.Append(", [Email] = @Email");
                    sqlQuery.Append(", [Status] = @Status");
                    sqlQuery.Append(" WHERE [OrderId] = @OrderId");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("UserId", SqlDbType.Int).Value = order.UserId;
                    command.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value = order.FirstName;
                    command.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value = order.LastName;
                    command.Parameters.Add("Address", SqlDbType.VarChar, 50).Value = order.Address;
                    command.Parameters.Add("Zip", SqlDbType.VarChar, 10).Value = order.Zip;
                    command.Parameters.Add("City", SqlDbType.VarChar, 50).Value = order.City;
                    command.Parameters.Add("Email", SqlDbType.VarChar, 50).Value = order.Email;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = order.Status;
                    command.Parameters.Add("OrderId", SqlDbType.Int).Value = order.OrderId;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static int DeleteOrderById(int orderId)
        {
            Order order = GetOrderById(orderId);
            foreach (OrderRow orderRow in order.OrderRows)
            {
                OrderRowDB.DeleteOrderRowById(orderRow.OrderRowId);
            }
            order.Status = "Deleted";
            return UpdateOrder(order);
            //using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            //{
            //    using (SqlCommand command = connection.CreateCommand())
            //    {
            //        var sqlQuery = new StringBuilder("DELETE FROM [dbo].[Orders]");
            //        sqlQuery.Append("WHERE [OrderId] = @OrderId");
            //        command.CommandText = sqlQuery.ToString();

            //        command.Parameters.Add("OrderId", SqlDbType.Int).Value = orderId;

            //        int affectedRows = command.ExecuteNonQuery();
            //        return affectedRows;
            //    }
            //}
        }
    }
}